package utils.utils;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

import org.apache.commons.lang.ArrayUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;

/**
 * 读取Excel
 *
 * @author sangbowei
 */
public class ReadExcelUtils {
    private Logger logger = LoggerFactory.getLogger(ReadExcelUtils.class);
    private Workbook wb;
    private Sheet sheet;
    private Row row;

    public ReadExcelUtils(String filepath) {
        if(filepath==null){
            return;
        }
        String ext = filepath.substring(filepath.lastIndexOf("."));
        try {
            InputStream is = new FileInputStream(filepath);
            if(".xls".equals(ext)){
                wb = new HSSFWorkbook(is);
            }else if(".xlsx".equals(ext)){
                wb = new XSSFWorkbook(is);
            }else{
                wb=null;
            }
        } catch (FileNotFoundException e) {
            logger.error("FileNotFoundException", e);
        } catch (IOException e) {
            logger.error("IOException", e);
        }
    }

    /**
     * 读取Excel表格表头的内容
     *
     * @return String 表头内容的数组
     * @author zengwendong
     */
    public String[] readExcelTitle() throws Exception{
        if(wb==null){
            throw new Exception("Workbook对象为空！");
        }
        sheet = wb.getSheetAt(0);
        row = sheet.getRow(0);
        // 标题总列数
        int colNum = row.getPhysicalNumberOfCells();
        System.out.println("colNum:" + colNum);
        String[] title = new String[colNum];
        for (int i = 0; i < colNum; i++) {
            // title[i] = getStringCellValue(row.getCell((short) i));
            title[i] = row.getCell(i).getCellFormula();
        }
        return title;
    }

    /**
     * 读取Excel数据内容
     *
     * @return Map 包含单元格数据内容的Map对象
     * @author zengwendong
     */
    public Map<Integer, Map<Integer,Object>> readExcelContent() throws Exception{
        if(wb==null){
            throw new Exception("Workbook对象为空！");
        }
        Map<Integer, Map<Integer,Object>> content = new HashMap<Integer, Map<Integer,Object>>();

        sheet = wb.getSheetAt(0);
        // 得到总行数
        int rowNum = sheet.getLastRowNum();
        row = sheet.getRow(0);
        int colNum = row.getPhysicalNumberOfCells();
        // 正文内容应该从第二行开始,第一行为表头的标题
        for (int i = 1; i <= rowNum; i++) {
            row = sheet.getRow(i);
            int j = 0;
            Map<Integer,Object> cellValue = new HashMap<Integer, Object>();
            while (j < colNum) {
                Object obj = getCellFormatValue(row.getCell(j));
                cellValue.put(j, obj);
                j++;
            }
            content.put(i, cellValue);
        }
        return content;
    }

    /**
     *
     * 根据Cell类型设置数据
     *
     * @param cell
     * @return
     * @author zengwendong
     */
    private Object getCellFormatValue(Cell cell) {
        Object cellvalue = "";
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:// 如果当前Cell的Type为NUMERIC
                case Cell.CELL_TYPE_FORMULA: {
                    // 判断当前的cell是否为Date
                    if (DateUtil.isCellDateFormatted(cell)) {
                        // 如果是Date类型则，转化为Data格式
                        // data格式是带时分秒的：2013-7-10 0:00:00
                        // cellvalue = cell.getDateCellValue().toLocaleString();
                        // data格式是不带带时分秒的：2013-7-10
                        Date date = cell.getDateCellValue();
                        cellvalue = date;
                    } else {// 如果是纯数字

                        // 取得当前Cell的数值
                        cellvalue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                case Cell.CELL_TYPE_STRING:// 如果当前Cell的Type为STRING
                    // 取得当前的Cell字符串
                    cellvalue = cell.getRichStringCellValue().getString();
                    break;
                default:// 默认的Cell值
                    cellvalue = "";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;
    }


    public static List<Long> getOrderIdListFromExcelFile(){

        List<Long> orderIdList = new ArrayList<Long>();
        try {
            String filepath = "E:/local_projects/sbwJavaBaseUtils/code/SbwJavaBaseUtils/src/sources/order.xlsx";
            ReadExcelUtils excelReader = new ReadExcelUtils(filepath);

            // 对读取Excel表格内容测试
            Map<Integer, Map<Integer,Object>> map = excelReader.readExcelContent();
            System.out.println("获得Excel表格的内容:");
            for (int i = 1; i <= map.size(); i++) {

                Map<Integer,Object> data = map.get(i);
                if(CollectionUtils.isEmpty(data)){
                    System.out.println(" Map data is null, i="+i);
                }
                Object orderId = data.get(0);
                orderIdList.add(Long.parseLong(orderId.toString()));
            }
        } catch (FileNotFoundException e) {
            System.out.println("未找到指定路径的文件!");
            e.printStackTrace();
        }catch (Exception e) {
            e.printStackTrace();
        }

        return orderIdList;
    }

    public static List<Long> getorderItemIdListFromExcelFile(){

        List<Long> orderItemIdList = new ArrayList<Long>();
        try {
            String filepath = "E:/local_projects/sbwJavaBaseUtils/code/SbwJavaBaseUtils/src/sources/orderItem.xlsx";
            ReadExcelUtils excelReader = new ReadExcelUtils(filepath);

            // 对读取Excel表格内容测试
            Map<Integer, Map<Integer,Object>> map = excelReader.readExcelContent();
            System.out.println("获得Excel表格的内容:");
            for (int i = 1; i <= map.size(); i++) {

                Map<Integer,Object> data = map.get(i);
                if(CollectionUtils.isEmpty(data)){
                    System.out.println(" Order item Map data is null, i="+i);
                }
                Object orderId = data.get(0);
                orderItemIdList.add(Long.parseLong(orderId.toString()));
            }
        } catch (FileNotFoundException e) {
            System.out.println("未找到指定路径的文件!");
            e.printStackTrace();
        }catch (Exception e) {
            e.printStackTrace();
        }

        return orderItemIdList;

    }

    public static List<Long> getOrderItemIdListFromData(){

        Long[] orderItemIdArr = {
                49016091L,
                49028155L,
                49028486L,
                49028487L,
                49028489L,
                49028494L,
                49028535L,
                49028558L,
                49028663L,
                49028686L,
                49028779L,
                49028859L,
                49028934L,
                49029002L,
                49029324L,
                49029727L,
                49030045L,
                49043319L,
                49043348L,
                49043886L,
                49044026L,
                49052741L,
                49053439L,
                49053953L,
                49062140L,
                49062413L,
                49063414L,
                49074189L,
                49075405L,
                49075787L,
                49075826L,
                49075875L,
                49075913L,
                49075952L,
                49076002L,
                49082540L,
                49082620L,
                49082660L,
                49082772L,
                49082878L,
                49082911L,
                49082914L,
                49082983L,
                49083247L,
                49084110L,
                49092162L,
                49092776L,
                49093093L,
                49093175L,
                49093192L,
                49093193L,
                49093197L,
                49093206L,
                49093416L,
                49093531L,
                49093614L,
                49093629L,
                49093645L,
                49093702L,
                49093792L,
                49093813L,
                49093939L,
                49093994L,
                49094033L,
                49108386L,
                49108448L,
                49108566L,
                49108759L,
                49108862L,
                49109111L,
                49109116L,
                49109157L,
                49109178L,
                49109401L,
                49109462L,
                49109520L,
                49109540L,
                49109764L,
                49109869L,
                49109887L,
                49109969L,
                49128260L,
                49128343L,
                49128389L,
                49128664L,
                49128889L,
                49128902L,
                49128996L,
                49129011L,
                49129039L,
                49129285L,
                49129390L,
                49129704L,
                49129763L,
                49129768L,
                49129829L,
                49140235L,
                49140276L,
                49140633L,
                49141182L,
                49142018L,
                49152294L,
                49152733L,
                49153001L,
                49153148L,
                49153395L,
                49153535L,
                49153800L,
                49153836L,
                49154008L,
                49154040L,
                49154096L,
                49166141L,
                49166796L,
                49166877L,
                49166954L,
                49167175L,
                49167213L,
                49167275L,
                49167329L,
                49167513L,
                49167583L,
                49167938L,
                49168002L,
                49168048L,
                49176533L,
                49176609L,
                49176688L,
                49177045L,
                49177122L,
                49177536L,
                49177596L,
                49177824L,
                49177847L,
                49177993L,
                49178007L,
                49178056L,
                49178082L,
                49178087L,
                49184123L,
                49184377L,
                49184581L,
                49184584L,
                49184625L,
                49184631L,
                49184657L,
                49184838L,
                49184856L,
                49184955L,
                49185092L,
                49185157L,
                49185181L,
                49185219L,
                49185274L,
                49185374L,
                49185435L,
                49185539L,
                49185592L,
                49185622L,
                49185649L,
                49185748L,
                49185835L,
                49185885L,
                49186042L,
                49186046L,
                49186051L,
                49186063L,
                49186074L,
                49186101L,
                49206122L,
                49206162L,
                49206177L,
                49206245L,
                49206572L,
                49206585L,
                49206594L,
                49206754L,
                49206761L,
                49206801L,
                49206817L,
                49207048L,
                49207222L,
                49207292L,
                49207329L,
                49207451L,
                49207492L,
                49207648L,
                49207666L,
                49207729L,
                49207860L,
                49207874L,
                49208015L,
                49226465L,
                49226708L,
                49226959L,
                49226988L,
                49226992L,
                49227038L,
                49227093L,
                49227205L,
                49227206L,
                49227699L,
                49227817L,
                49227894L,
                49228033L,
                49228102L,
                49236147L,
                49236402L,
                49236555L,
                49236802L,
                49236946L,
                49236958L,
                49237056L,
                49237209L,
                49237376L,
                49237388L,
                49237823L,
                49237846L,
                49237974L,
                49238077L,
                49246255L,
                49246753L,
                49246813L,
                49247098L,
                49247386L,
                49247443L,
                49247513L,
                49247558L,
                49247684L,
                49248095L,
                49256360L,
                49256560L,
                49257253L,
                49257458L,
                49257519L,
                49257547L,
                49257614L,
                49257827L,
                49257828L,
                49258069L,
                49266151L,
                49266304L,
                49266313L,
                49266436L,
                49266675L,
                49267142L,
                49267169L,
                49267435L,
                49267536L,
                49267762L,
                49274254L,
                49274379L,
                49274424L,
                49274712L,
                49274720L,
                49274956L,
                49274982L,
                49274996L,
                49274998L,
                49275021L,
                49275041L,
                49275052L,
                49275110L,
                49275126L,
                49275353L,
                49275914L,
                49288497L,
                49288778L,
                49289431L,
                49289945L,
                49304114L,
                49304237L,
                49304304L,
                49304574L,
                49304596L,
                49304625L,
                49304636L,
                49304982L,
                49305087L,
                49305127L,
                49305223L,
                49305246L,
                49305420L,
                49305879L,
                49305925L,
                49324224L,
                49324233L,
                49324289L,
                49324334L,
                49324416L,
                49324703L,
                49324756L,
                49325389L,
                49325760L,
                49326010L,
                49342650L,
                49342864L,
                49342898L,
                49342926L,
                49343034L,
                49343528L,
                49343605L,
                49343643L,
                49343647L,
                49343875L,
                49352276L,
                49352320L,
                49352529L,
                49352637L,
                49352769L,
                49352895L,
                49352939L,
                49352993L,
                49353039L,
                49353057L,
                49353112L,
                49353115L,
                49353500L,
                49353654L,
                49353745L,
                49353885L,
                49353913L,
                49353926L,
                49354031L,
                49366343L,
                49366471L,
                49366535L,
                49366572L,
                49366678L,
                49366818L,
                49366917L,
                49367143L,
                49367269L,
                49367806L,
                49367905L,
                49367915L,
                49382240L,
                49382300L,
                49382404L,
                49382617L,
                49382636L,
                49382671L,
                49382692L,
                49382725L,
                49382767L,
                49382969L,
                49383025L,
                49383160L,
                49383164L,
                49383200L,
                49383438L,
                49383441L,
                49383482L,
                49383726L,
                49383772L,
                49383979L,
                49394169L,
                49394211L,
                49394335L,
                49394667L,
                49394686L,
                49394706L,
                49394736L,
                49394745L,
                49394877L,
                49394977L,
                49395031L,
                49395176L,
                49395710L,
                49395727L,
                49395730L,
                49395860L,
                49422127L,
                49422275L,
                49422305L,
                49422343L,
                49422460L,
                49422517L,
                49422576L,
                49422730L,
                49422738L,
                49422791L,
                49422804L,
                49422991L,
                49422992L,
                49423219L,
                49423298L,
                49423410L,
                49423455L,
                49423488L,
                49423493L,
                49423513L,
                49423624L,
                49423663L,
                49423686L,
                49423768L,
                49423792L,
                49423920L,
                49458176L,
                49458207L,
                49458353L,
                49458553L,
                49458786L,
                49458917L,
                49459052L,
                49459073L,
                49459260L,
                49459376L,
                49459528L,
                49459574L,
                49459620L,
                49459650L,
                49459759L,
                49459902L,
                49460070L,
                49486155L,
                49486158L,
                49486606L,
                49486678L,
                49486684L,
                49486998L,
                49487017L,
                49487147L,
                49487186L,
                49487395L,
                49487404L,
                49487465L,
                49487537L,
                49487755L,
                49487834L,
                49487854L,
                49514298L,
                49514520L,
                49514531L,
                49515032L,
                49515097L,
                49515220L,
                49515336L,
                49515364L,
                49515487L,
                49515495L,
                49515538L,
                49515574L,
                49515616L,
                49536191L,
                49536916L,
                49536990L,
                49537068L,
                49537266L,
                49537429L,
                49537441L,
                49537468L,
                49537500L,
                49537511L,
                49537517L,
                49537585L,
                49537643L,
                49537717L,
                49537805L,
                49537837L,
                49537865L,
                49574408L,
                49574412L,
                49574417L,
                49574423L,
                49574809L,
                49575078L,
                49575122L,
                49575180L,
                49575239L,
                49575307L,
                49575430L,
                49575503L,
                49575675L,
                49575730L,
                49575889L,
                49575932L,
                49576034L,
                49576047L,
                49576079L,
                49576107L,
                49612148L,
                49612225L,
                49612284L,
                49612396L,
                49612439L,
                49612597L,
                49612694L,
                49612703L,
                49612742L,
                49612942L,
                49613160L,
                49613209L,
                49613240L,
                49613394L,
                49613589L,
                49613600L,
                49613679L,
                49613680L,
                49614070L,
                49640177L,
                49640246L,
                49640364L,
                49640483L,
                49640881L,
                49640973L,
                49641081L,
                49641211L,
                49641243L,
                49641751L,
                49641773L,
                49641962L,
                49642026L,
                49658160L,
                49658310L,
                49658324L,
                49658391L,
                49658413L,
                49658490L,
                49658603L,
                49658666L,
                49658686L,
                49658972L,
                49658981L,
                49658983L,
                49659075L,
                49659179L,
                49659195L,
                49659280L,
                49659283L,
                49659360L,
                49659402L,
                49659491L,
                49659568L,
                49659575L,
                49659761L,
                49659824L,
                49659840L,
                49659958L,
                49678114L,
                49678119L,
                49678126L,
                49678207L,
                49678375L,
                49678553L,
                49678588L,
                49678667L,
                49678751L,
                49678761L,
                49679077L,
                49679082L,
                49679095L,
                49679155L,
                49679186L,
                49679212L,
                49679217L,
                49679226L,
                49679299L,
                49679440L,
                49679442L,
                49679492L,
                49679514L,
                49679525L,
                49679526L,
                49679542L,
                49679895L,
                49680004L,
                49704413L,
                49704498L,
                49704650L,
                49704695L,
                49705245L,
                49705330L,
                49705458L,
                49705576L,
                49705586L,
                49705595L,
                49705616L,
                49705708L,
                49705809L,
                49705978L,
                49706026L,
                49722191L,
                49722563L,
                49722572L,
                49722673L,
                49722695L,
                49722853L,
                49722880L,
                49722905L,
                49722910L,
                49722918L,
                49722924L,
                49723048L,
                49723108L,
                49723186L,
                49723309L,
                49723327L,
                49723381L,
                49723433L,
                49723487L,
                49723570L,
                49723696L,
                49723732L,
                49723835L,
                49723901L,
                49723903L,
                49723927L,
                49723971L,
                49723987L,
                49724027L,
                49738131L,
                49738162L,
                49738286L,
                49738558L,
                49738789L,
                49739138L,
                49739154L,
                49739310L,
                49739350L,
                49739697L,
                49739783L,
                49739906L,
                49750283L,
                49750475L,
                49750533L,
                49750538L,
                49750597L,
                49750683L,
                49750761L,
                49750770L,
                49750800L,
                49750806L,
                49750977L,
                49751102L,
                49751115L,
                49751157L,
                49751297L,
                49751338L,
                49751446L,
                49751673L,
                49751698L,
                49751746L,
                49751831L,
                49751973L,
                49766137L,
                49766188L,
                49766219L,
                49766256L,
                49766274L,
                49766299L,
                49766591L,
                49766600L,
                49766723L,
                49766899L,
                49767053L,
                49767123L,
                49767183L,
                49767204L,
                49767275L,
                49767281L,
                49767364L,
                49767398L,
                49767634L,
                49767684L,
                49767735L,
                49767803L,
                49767972L,
                49768051L,
                49768062L,
                49790120L,
                49790198L,
                49790452L,
                49790485L,
                49790486L,
                49790725L,
                49790830L,
                49790931L,
                49791087L,
                49791170L,
                49791224L,
                49791255L,
                49791398L,
                49791410L,
                49791417L,
                49791437L,
                49791447L,
                49791458L,
                49791478L,
                49791530L,
                49791567L,
                49791602L,
                49791782L,
                49791848L,
                49791855L,
                49791907L,
                49791925L,
                49810172L,
                49810654L,
                49810715L,
                49810750L,
                49810776L,
                49810809L,
                49810816L,
                49810844L,
                49811363L,
                49811365L,
                49811490L,
                49811569L,
                49811656L,
                49811664L,
                49811672L,
                49811676L,
                49811733L,
                49811801L,
                49812108L,
                49828401L,
                49828428L,
                49828585L,
                49828614L,
                49828766L,
                49828781L,
                49828788L,
                49828802L,
                49828807L,
                49828826L,
                49828874L,
                49828910L,
                49829133L,
                49829537L,
                49829542L,
                49829834L,
                49829901L,
                49829944L,
                49829974L,
                49830048L,
                49842158L,
                49842350L,
                49842480L,
                49842621L,
                49842629L,
                49842672L,
                49842760L,
                49842762L,
                49842896L,
                49843072L,
                49843144L,
                49843263L,
                49843577L,
                49843638L,
                49843649L,
                49843684L,
                49843844L,
                49843904L,
                49852147L,
                49852153L,
                49852325L,
                49852346L,
                49852506L,
                49852511L,
                49852542L,
                49852551L,
                49852557L,
                49852613L,
                49852656L,
                49852661L,
                49852705L,
                49852831L,
                49852873L,
                49853006L,
                49853021L,
                49853031L,
                49853038L,
                49853166L,
                49853247L,
                49853265L,
                49853455L,
                49853604L,
                49853768L,
                49853802L,
                49853814L,
                49853823L,
                49853890L,
                49854044L,
                49870205L,
                49870319L,
                49870478L,
                49870507L,
                49870529L,
                49870546L,
                49870593L,
                49870655L,
                49870793L,
                49870817L,
                49870829L,
                49870850L,
                49871067L,
                49871159L,
                49871206L,
                49871212L,
                49871333L,
                49871481L,
                49871518L,
                49871524L,
                49871551L,
                49871553L,
                49871573L,
                49871664L,
                49871684L,
                49871693L,
                49871731L,
                49871768L,
                49871771L,
                49871817L,
                49871841L,
                49871881L,
                49871978L,
                49872024L,
                49872040L,
                49892141L,
                49892353L,
                49892380L,
                49892434L,
                49892664L,
                49892726L,
                49892784L,
                49892828L,
                49892933L,
                49892986L,
                49893039L,
                49893105L,
                49893188L,
                49893226L,
                49893380L,
                49893401L,
                49893412L,
                49893432L,
                49893501L,
                49893617L,
                49893671L,
                49893685L,
                49893700L,
                49893706L,
                49893956L,
                49894030L,
                49894067L,
                49894091L,
                49912241L,
                49912255L,
                49912266L,
                49912311L,
                49912417L,
                49912446L,
                49912516L,
                49912591L,
                49912691L,
                49912711L,
                49912789L,
                49912799L,
                49913356L,
                49913431L,
                49913517L,
                49913800L,
                49914010L,
                49914106L,
                49930196L,
                49930341L,
                49930410L,
                49930536L,
                49930586L,
                49930763L,
                49930905L,
                49931000L,
                49931033L,
                49931238L,
                49931358L,
                49931381L,
                49931406L,
                49931543L,
                49931582L,
                49931601L,
                49931707L,
                49931746L,
                49931860L,
                49931873L,
                49931911L,
                49931915L,
                49931947L,
                49944236L,
                49944879L,
                49944964L,
                49945315L,
                49945535L,
                49945539L,
                49945550L,
                49945627L,
                49945666L,
                49945748L,
                49945767L,
                49945782L,
                49945830L,
                49945848L,
                49945858L,
                49958241L,
                49958468L,
                49958541L,
                49958546L,
                49958554L,
                49958640L,
                49958643L,
                49958713L,
                49958760L,
                49958791L,
                49958819L,
                49958960L,
                49958975L,
                49958978L,
                49959015L,
                49959051L,
                49959184L,
                49959283L,
                49959425L,
                49959484L,
                49959548L,
                49959576L,
                49959583L,
                49959609L,
                49959634L,
                49959671L,
                49959681L,
                49959689L,
                49959702L,
                49959746L,
                49959796L,
                49959832L,
                49959958L,
                49980165L,
                49980183L,
                49980246L,
                49980442L,
                49980459L,
                49980580L,
                49980759L,
                49981002L,
                49981043L,
                49981090L,
                49981098L,
                49981329L,
                49981447L,
                49981577L,
                49981849L,
                49982005L,
                49982064L,
                50002231L,
                50002241L,
                50002339L,
                50002421L,
                50002628L,
                50002722L,
                50002829L,
                50003101L,
                50003184L,
                50003357L,
                50003515L,
                50003555L,
                50003572L,
                50003619L,
                50003647L,
                50004032L,
                50004055L,
                50022142L,
                50022349L,
                50022462L,
                50023163L,
                50023169L,
                50023411L,
                50023517L,
                50023556L,
                50023716L,
                50023739L,
                50023743L,
                50023744L,
                50023745L,
                50023747L,
                50023748L,
                50023749L,
                50023810L,
                50023835L,
                50023942L,
                50040146L,
                50040547L,
                50040562L,
                50040620L,
                50040720L,
                50040750L,
                50040773L,
                50040780L,
                50040878L,
                50040894L,
                50041427L
        };
        List<Long> orderItemIdList = Arrays.asList(orderItemIdArr);
        return orderItemIdList;

    }

    public static List<Long> getOrderIdList2(){

        Long[] orderItemIdArr = {
                49258598L,
                49385046L,
                49466413L,
                49471170L,
                49612820L,
                49640309L,
                49724053L,
                49782561L,
                49783075L,
                49872545L,
                49880016L,
                49880922L,
                49898609L,
                49898609L,
                49910093L,
                49918552L,
                49918782L,
                49913646L,
                49913965L,
                49914019L,
                49914083L,
                49914086L,
                49929714L,
                49914098L,
                48449694L,
                48449694L,
                49929846L,
                49932145L,
                49932157L,
                49932165L,
                49930152L,
                49932199L,
                49932203L,
                49932222L,
                49932545L,
                49930214L,
                49932976L
        };
        List<Long> orderIdList = Arrays.asList(orderItemIdArr);
        return orderIdList;

    }
}